package com.edu.app.mapper.ai;

import com.edu.app.bean.core.SysUser;
import com.edu.app.bean.virtualbean.KeyValueItems;
import com.edu.app.bean.virtualbean.NameValueItems;
import com.edu.app.bean.virtualbean.customize.PoCustomerStatus;
import com.edu.app.bean.virtualbean.customize.SalesCommon;
import com.edu.app.bean.virtualbean.customize.SalesCustomerStatus;
import com.edu.app.bean.virtualbean.customize.SalesPerf;
import com.edu.app.model.customer.CustomerNotAssignedModel;
import com.edu.app.util.AppBaseMapper;

import com.edu.app.model.customer.CustomerNotActiveModel;

import org.apache.ibatis.annotations.Param;
import org.apache.ibatis.annotations.Select;
import org.springframework.stereotype.Component;

import java.util.List;

@Component
public interface SysAISalesMapper extends AppBaseMapper<SysUser> {
    @Select("select id,name,stage, " +
            "case when DATEDIFF(recent_trace_days, recent_activity_days)>0 then recent_trace_days else recent_activity_days end as not_active_days " +
            "from (select distinct t1.id,t1.name, t6.value as stage, DATEDIFF(now(), max(t2.create_time)) as recent_trace_days,DATEDIFF(now(), max(t7.time)) as recent_activity_days from s_customer t1 " +
            "inner join s_user_customer t4 on t1.id = t4.customer_id " +
            "inner join s_user t5 on t4.user_id = t5.id " +
            "left join s_list_of_value t6 on t1.stage_id = t6.id " +
            "left join s_customer_trace t2 on t1.id = t2.customer_id " +
            "left join s_customer_activity t3 on t1.id = t3.customer_id " +
            "left join s_activity t7 on t3.activity_id = t7.id " +
            "where t5.username=#{username} and t1.status='0' and t4.roleplay='consultant' group by t1.id,t1.name,t6.value) tb1 " +
            "where (recent_trace_days>#{range} and recent_activity_days>#{range}) " +
            "or (recent_trace_days is null and recent_activity_days is null) " +
            "or (recent_trace_days >#{range} and recent_activity_days is null) " +
            "or (recent_trace_days is null and recent_activity_days>#{range})")
    List<CustomerNotActiveModel> getNotActiveCustomerByUser(@Param("username") String username, @Param("range") Integer range);

    @Select("select id,name,group_concat(sales separator ',' ) as sales,stage," +
            "case when DATEDIFF(recent_trace_days, recent_activity_days)>0 then recent_trace_days else recent_activity_days end as not_active_days " +
            "from (select distinct t5.nickname as sales,t1.id,t1.name, t6.value as stage, " +
            "DATEDIFF(now(), max(t2.create_time)) as recent_trace_days," +
            "DATEDIFF(now(), max(t7.time)) as recent_activity_days " +
            "from s_customer t1 " +
            "inner join s_user_customer t4 on t1.id = t4.customer_id " +
            "inner join s_user t5 on t4.user_id = t5.id " +
            "left join s_list_of_value t6 on t1.stage_id = t6.id " +
            "left join s_customer_trace t2 on t1.id = t2.customer_id " +
            "left join s_customer_activity t3 on t1.id = t3.customer_id " +
            "left join s_activity t7 on t3.activity_id = t7.id " +
            "where t1.status='0' and t4.roleplay='consultant' group by t1.id,t1.name,t6.value,t5.nickname) tb1 " +
            "where (recent_trace_days>#{range} and recent_activity_days>#{range}) " +
            "or (recent_trace_days is null and recent_activity_days is null) " +
            "or (recent_trace_days >#{range} and recent_activity_days is null) " +
            "or (recent_trace_days is null and recent_activity_days>#{range}) " +
            "group by id,name,stage,recent_trace_days, recent_activity_days")
    List<CustomerNotActiveModel> getNotActiveCustomer(Integer range);


    @Select("select distinct t1.id as 'key', t1.name as value from s_customer t1 " +
            "inner join s_customer_activity t2 on t1.id = t2.customer_id " +
            "inner join s_activity t3 on t3.id = t2.activity_id " +
            "inner join s_user_customer t4 on t1.id = t4.customer_id " +
            "inner join s_user t5 on t5.id = t4.user_id " +
            "where t5.username = #{username} and t4.roleplay='consultant' and date(t3.time) = date(now())")
    List<KeyValueItems> getCustomerWithDemoClassToday(String username);


    @Select("select distinct t1.id, t1.name, t3.time as value from s_customer t1 " +
            "inner join s_customer_activity t2 on t1.id = t2.customer_id " +
            "inner join s_activity t3 on t3.id = t2.activity_id " +
            "inner join s_user_customer t4 on t1.id = t4.customer_id " +
            "inner join s_user t5 on t5.id = t4.user_id " +
            "where t5.username = ${username} and t4.roleplay='consultant' and date(t3.time) > date(now())")
    List<NameValueItems> getCustomerWithDemoClassFuture(@Param("username") String username);

    @Select("select rank as id, username as name, number as value from " +
            "(select username, number, @curRank := @curRank + 1 as rank from " +
            "(select username, count(*) as number from (select distinct t1.username,t3.name,t5.time,t6.value from s_user t1 " +
            "inner join s_user_customer t2 on t1.id = t2.user_id " +
            "inner join s_customer t3 on t3.id = t2.customer_id " +
            "inner join s_customer_activity t4 on t3.id = t4.customer_id " +
            "inner join s_activity t5 on t5.id = t4.activity_id " +
            "inner join s_list_of_value t6 on t5.type_id = t6.id " +
            "WHERE MONTH(t5.time) = MONTH(Now()) and t6.value='右脑试听课' and t2.roleplay='consultant') tb1 group by username order by number desc) tb2, (\n" +
            "select @curRank :=0 tb3) tb4 ) tb5 " +
            "where username=#{username}")
    NameValueItems getSalesDemoClassRank(String username);

    @Select("select id,name,value from (select name, value, @curRank := @curRank + 1 as id from " +
            "(select t1.username as name, count(*) as value from s_user t1 inner join s_contract t2 on t1.id = t2.user_id group by name order by value desc) tb1, " +
            "(select @curRank :=0 tb2) tb3 ) tb4 " +
            "where name = #{username}")
    NameValueItems getSalesContractNumber(String username);

    @Select("select rank as id, username as name, total_amount as value from " +
            "(select username, total_amount, @curRank := @curRank + 1 as rank from " +
            "(select t1.username, sum(t2.amount) as total_amount from s_user t1 inner join s_contract t2 on t1.id = t2.user_id group by t1.username order by total_amount desc) tb1, " +
            "(select @curRank :=0 tb2) tb3 ) tb4 " +
            "where username=#{username}")
    NameValueItems getSalesContractTotalAmount(String username);

    @Select("select rank as id, nickname as name, number as value from " +
            "(select nickname, number, @curRank := @curRank + 1 as rank from " +
            "(select nickname, count(*) as number from (select distinct t1.nickname,t3.name,t5.time,t6.value from s_user t1 " +
            "inner join s_user_customer t2 on t1.id = t2.user_id " +
            "inner join s_customer t3 on t3.id = t2.customer_id " +
            "inner join s_customer_activity t4 on t3.id = t4.customer_id " +
            "inner join s_activity t5 on t5.id = t4.activity_id " +
            "inner join s_list_of_value t6 on t5.type_id = t6.id " +
            "WHERE DATE(t5.time) = DATE(Now()) and t6.value='右脑试听课' and t2.roleplay='consultant') tb1 group by nickname order by number desc) tb2, (" +
            "select @curRank :=0 tb3) tb4 ) tb5 ")
    List<NameValueItems> getSalesDemoClassRankAllByDay();

    @Select("select rank as id, nickname as name, number as value from " +
            "(select nickname, number, @curRank := @curRank + 1 as rank from " +
            "(select nickname, count(*) as number from (select distinct t1.nickname,t3.name,t5.time,t6.value from s_user t1 " +
            "inner join s_user_customer t2 on t1.id = t2.user_id " +
            "inner join s_customer t3 on t3.id = t2.customer_id " +
            "inner join s_customer_activity t4 on t3.id = t4.customer_id " +
            "inner join s_activity t5 on t5.id = t4.activity_id " +
            "inner join s_list_of_value t6 on t5.type_id = t6.id " +
            "WHERE YEAR(t5.time)=YEAR(now()) and WEEK(t5.time,1) = WEEK(Now(),1) and t6.value='右脑试听课' and t2.roleplay='consultant') tb1 group by nickname order by number desc) tb2, (" +
            "select @curRank :=0 tb3) tb4 ) tb5 ")
    List<NameValueItems> getSalesDemoClassRankAllByWeek();

    @Select("select rank as id, nickname as name, number as value from " +
            "(select nickname, number, @curRank := @curRank + 1 as rank from " +
            "(select nickname, count(*) as number from (select distinct t1.nickname,t3.name,t5.time,t6.value from s_user t1 " +
            "inner join s_user_customer t2 on t1.id = t2.user_id " +
            "inner join s_customer t3 on t3.id = t2.customer_id " +
            "inner join s_customer_activity t4 on t3.id = t4.customer_id " +
            "inner join s_activity t5 on t5.id = t4.activity_id " +
            "inner join s_list_of_value t6 on t5.type_id = t6.id " +
            "WHERE YEAR(t5.time)=YEAR(now()) and MONTH(t5.time) = MONTH(Now()) and t6.value='右脑试听课' and t2.roleplay='consultant') tb1 group by nickname order by number desc) tb2, (" +
            "select @curRank :=0 tb3) tb4 ) tb5 ")
    List<NameValueItems> getSalesDemoClassRankAllByMonth();


    @Select("select id,name,value from (select name, value, @curRank := @curRank + 1 as id from " +
            "(select t1.nickname as name, count(*) as value from s_user t1 inner join s_contract t2 on t1.id = t2.user_id " +
            "where DATE(t2.start_date)=DATE(now()) group by name order by value desc) tb1, " +
            "(select @curRank :=0 tb2) tb3 ) tb4 ")
    List<NameValueItems> getSalesContractNumberAllByDay();

    @Select("select id,name,value from (select name, value, @curRank := @curRank + 1 as id from " +
            "(select t1.nickname as name, count(*) as value from s_user t1 inner join s_contract t2 on t1.id = t2.user_id " +
            "where YEAR(t2.start_date)=YEAR(now()) and MONTH(t2.start_date)=MONTH(now()) group by name order by value desc) tb1, " +
            "(select @curRank :=0 tb2) tb3 ) tb4 ")
    List<NameValueItems> getSalesContractNumberAllByWeek();

    @Select("select id,name,value from (select name, value, @curRank := @curRank + 1 as id from " +
            "(select t1.nickname as name, count(*) as value from s_user t1 inner join s_contract t2 on t1.id = t2.user_id " +
            "where YEAR(t2.start_date)=YEAR(now()) and MONTH(t2.start_date)=MONTH(now()) group by name order by value desc) tb1, " +
            "(select @curRank :=0 tb2) tb3 ) tb4 ")
    List<NameValueItems> getSalesContractNumberAllByMonth();

    @Select("select rank as id, nickname as name, total_amount as value from " +
            "(select nickname, total_amount, @curRank := @curRank + 1 as rank from " +
            "(select t1.nickname, sum(t2.amount) as total_amount from s_user t1 inner join s_contract t2 on t1.id = t2.user_id " +
            "where DATE(t2.start_date)=DATE(now()) group by t1.username order by total_amount desc) tb1, " +
            "(select @curRank :=0 tb2) tb3 ) tb4 ")
    List<NameValueItems> getSalesContractTotalAmountAllByDay();

    @Select("select rank as id, nickname as name, total_amount as value from " +
            "(select nickname, total_amount, @curRank := @curRank + 1 as rank from " +
            "(select t1.nickname, sum(t2.amount) as total_amount from s_user t1 inner join s_contract t2 on t1.id = t2.user_id " +
            "where YEAR(t2.start_date)=YEAR(now()) and WEEK(t2.start_date)=WEEK(now()) group by t1.username order by total_amount desc) tb1, " +
            "(select @curRank :=0 tb2) tb3 ) tb4 ")
    List<NameValueItems> getSalesContractTotalAmountAllByWeek();

    @Select("select rank as id, nickname as name, total_amount as value from " +
            "(select nickname, total_amount, @curRank := @curRank + 1 as rank from " +
            "(select t1.nickname, sum(t2.amount) as total_amount from s_user t1 inner join s_contract t2 on t1.id = t2.user_id " +
            "where YEAR(t2.start_date)=YEAR(#{date}) and MONTH(t2.start_date)=MONTH(#{date}) group by t1.username order by total_amount desc) tb1, " +
            "(select @curRank :=0 tb2) tb3 ) tb4 ")
    List<NameValueItems> getSalesContractTotalAmountAllByMonth(String date);

    @Select("select create_time,create_by,id,tag,substring_index(GROUP_CONCAT(time SEPARATOR '|'),'|',1) as time,name,birthday, " +
            "description,nickname as consultant,substring_index(GROUP_CONCAT(comments SEPARATOR '|'),'|',1) as comments,stage from " +
            "(select distinct t1.create_time,t8.nickname as create_by,t1.tag,t1.id, " +
            "concat_ws(' ',t5.time,t9.value) as time,t1.name,t1.birthday,t1.description,t3.nickname, " +
            "concat_ws(' ',t6.create_time,t6.content) as comments,t7.value as stage from s_customer t1 " +
            "inner join s_user_customer t2 on t1.id = t2.customer_id " +
            "inner join s_user t3 on t2.user_id = t3.id " +
            "inner join s_user t8 on t8.username = t1.create_by " +
            "left join s_customer_activity t4 on t1.id = t4.customer_id " +
            "left join s_activity t5 on t4.activity_id = t5.id " +
            "left join s_customer_trace t6 on t1.id = t6.customer_id " +
            "left join s_list_of_value t7 on t1.stage_id = t7.id " +
            "left join s_list_of_value t9 on t5.type_id = t9.id " +
            "where (t7.value like #{stage} or t7.value is null) and " +
            "t2.roleplay='consultant' and t1.status='0' order by time desc, comments desc) tb1 group by id,name,birthday,description,nickname,stage order by time desc")
    List<PoCustomerStatus> getPoCustomerNotStartByStage(@Param("stage") String stage);

    /*@Select("select phone,DATEDIFF(NOW(),create_time) as days,create_by,id,tag,substring_index(GROUP_CONCAT(time SEPARATOR '|'),'|',1) as time,name,DATE_FORMAT(FROM_DAYS(TO_DAYS(NOW())-TO_DAYS(birthday)), '%Y')+0 AS age, " +
            "description,nickname as consultant,substring_index(GROUP_CONCAT(comments SEPARATOR '|'),'|',1) as comments,stage from " +
            "(select distinct t1.phone,t1.create_time,t8.nickname as create_by,t1.tag,t1.id, " +
            "concat_ws(' ',t5.time,t9.value) as time,t1.name,t1.birthday,t1.description,t3.nickname, " +
            "concat_ws(' ',t6.create_time,t6.content) as comments,t7.value as stage from s_customer t1 " +
            "inner join s_user_customer t2 on t1.id = t2.customer_id " +
            "inner join s_user t3 on t2.user_id = t3.id " +
            "inner join s_user t8 on t8.username = t1.create_by " +
            "left join s_customer_activity t4 on t1.id = t4.customer_id " +
            "left join s_activity t5 on t4.activity_id = t5.id " +
            "left join s_customer_trace t6 on t1.id = t6.customer_id " +
            "left join s_list_of_value t7 on t1.stage_id = t7.id " +
            "left join s_list_of_value t9 on t5.type_id = t9.id " +
            "where " +
            "t2.roleplay='consultant' and t1.status=#{status} and t3.username=#{username} order by time desc, comments desc) tb1 group by id,name,birthday,description,nickname,stage order by ${orderBy} ${sequence}")
    List<PoCustomerStatus> getMyCustomerModel(@Param("username") String username, @Param("orderBy") String orderBy, @Param("sequence") String sequence, @Param("status") Integer status);*/

    @Select("select substring_index(GROUP_CONCAT(time SEPARATOR '|'),'|',1) as time," +
            "substring_index(GROUP_CONCAT(comments SEPARATOR '|'),'|',1) as comments," +
            "employee,quality_tag,channel_tag,contract_time,amount,id,days,tag,name,stage,phone,age from " +
            "(select distinct t1.nickname as employee,t3.quality_tag,t3.channel_tag,t6.start_date as contract_time,t6.amount,t5.create_time as time," +
            "t5.content as comments,t3.id,DATEDIFF(NOW(),t3.create_time) as days," +
            "t3.tag,t3.name,t4.value as stage, t3.phone," +
            "DATE_FORMAT(FROM_DAYS(TO_DAYS(NOW()) - TO_DAYS(birthday)), '%Y') + 0 AS age from s_user t1 " +
            "inner join s_user_customer t2 on t1.id = t2.user_id " +
            "inner join s_customer t3 on t3.id = t2.customer_id " +
            "left join s_list_of_value t4 on t3.stage_id = t4.id " +
            "left join s_customer_trace t5 on t3.id = t5.customer_id " +
            "left join s_contract t6 on t3.id = t6.customer_id " +
            "where t1.username=#{username} and t3.status = ${status} order by t3.id asc,t5.create_time desc) tb " +
            "group by employee,contract_time,amount,id,days,tag,name,stage,phone,age order by ${orderBy} ${sequence}")
    List<SalesCustomerStatus> getMyCustomerModel(@Param("username") String username, @Param("orderBy") String orderBy, @Param("sequence") String sequence, @Param("status") Integer status);

    @Select("select substring_index(GROUP_CONCAT(time SEPARATOR '|'),'|',1) as time," +
            "substring_index(GROUP_CONCAT(comments SEPARATOR '|'),'|',1) as comments," +
            "employee,quality_tag,channel_tag,contract_time,amount,id,days,tag,name,stage,phone,age from " +
            "(select distinct t1.nickname as employee,t3.quality_tag,t3.channel_tag,t6.start_date as contract_time,t6.amount,t5.create_time as time," +
            "t5.content as comments,t3.id,DATEDIFF(NOW(),t3.create_time) as days," +
            "t3.tag,t3.name,t4.value as stage, t3.phone," +
            "DATE_FORMAT(FROM_DAYS(TO_DAYS(NOW()) - TO_DAYS(birthday)), '%Y') + 0 AS age from s_user t1 " +
            "inner join s_user_customer t2 on t1.id = t2.user_id " +
            "inner join s_customer t3 on t3.id = t2.customer_id " +
            "left join s_list_of_value t4 on t3.stage_id = t4.id " +
            "left join s_customer_trace t5 on t3.id = t5.customer_id " +
            "left join s_contract t6 on t3.id = t6.customer_id " +
            "where t2.roleplay='consultant' and t3.status = 0 order by t3.id asc,t5.create_time desc) tb " +
            "group by employee,contract_time,amount,id,days,tag,name,stage,phone,age order by ${orderBy} ${sequence}")
    List<SalesCustomerStatus> getAllPoCustomerModel(@Param("orderBy") String orderBy, @Param("sequence") String sequence, @Param("status") Integer status);

    @Select("select substring_index(GROUP_CONCAT(time SEPARATOR '|'),'|',1) as time," +
            "substring_index(GROUP_CONCAT(comments SEPARATOR '|'),'|',1) as comments," +
            "employee,quality_tag,channel_tag,contract_time,amount,id,days,tag,name,stage,phone,age from " +
            "(select distinct t1.nickname as employee,t3.quality_tag,t3.channel_tag,t6.start_date as contract_time,t6.amount,t5.create_time as time," +
            "t5.content as comments,t3.id,DATEDIFF(NOW(),t3.create_time) as days," +
            "t3.tag,t3.name,t4.value as stage, t3.phone," +
            "DATE_FORMAT(FROM_DAYS(TO_DAYS(NOW()) - TO_DAYS(birthday)), '%Y') + 0 AS age from s_user t1 " +
            "inner join s_user_customer t2 on t1.id = t2.user_id " +
            "inner join s_customer t3 on t3.id = t2.customer_id " +
            "left join s_list_of_value t4 on t3.stage_id = t4.id " +
            "left join s_customer_trace t5 on t3.id = t5.customer_id " +
            "left join s_contract t6 on t3.id = t6.customer_id " +
            "where t2.roleplay='consultant' and t3.status = 0 order by t3.id asc,t5.create_time desc) tb " +
            "group by employee,contract_time,amount,id,days,tag,name,stage,phone,age order by ${orderBy} ${sequence}")
    List<SalesCustomerStatus> getAllDemoClassPoCustomerModel(@Param("orderBy") String orderBy, @Param("sequence") String sequence, @Param("status") Integer status);

    @Select("select DATEDIFF(NOW(),create_time) as days,create_by,id,tag,substring_index(GROUP_CONCAT(time SEPARATOR '|'),'|',1) as time,name,DATE_FORMAT(FROM_DAYS(TO_DAYS(NOW())-TO_DAYS(birthday)), '%Y')+0 AS age, " +
            "description,nickname as consultant,substring_index(GROUP_CONCAT(comments SEPARATOR '|'),'|',1) as comments,stage from " +
            "(select distinct t1.create_time,t8.nickname as create_by,t1.tag,t1.id, " +
            "concat_ws(' ',t5.time,t9.value) as time,t1.name,t1.birthday,t1.description,t3.nickname, " +
            "concat_ws(' ',t6.create_time,t6.content) as comments,t7.value as stage from s_customer t1 " +
            "inner join s_user_customer t2 on t1.id = t2.customer_id " +
            "inner join s_user t3 on t2.user_id = t3.id " +
            "inner join s_user t8 on t8.username = t1.create_by " +
            "left join s_customer_activity t4 on t1.id = t4.customer_id " +
            "left join s_activity t5 on t4.activity_id = t5.id " +
            "left join s_customer_trace t6 on t1.id = t6.customer_id " +
            "left join s_list_of_value t7 on t1.stage_id = t7.id " +
            "left join s_list_of_value t9 on t5.type_id = t9.id " +
            "where " +
            "t2.roleplay='consultant' and t1.status='0' order by time desc, comments desc) tb1 group by id,name,birthday,description,nickname,stage order by ${orderBy} ${sequence}")
    List<PoCustomerStatus> getPoCustomerModel(@Param("orderBy") String orderBy, @Param("sequence") String sequence);

    @Select("select t1.id,t1.nickname as name,t4.value as type,count(t4.value) as value from s_user t1 " +
            "inner join s_user_activity t2 on t1.id = t2.user_id " +
            "inner join s_activity t3 on t3.id = t2.activity_id " +
            "inner join s_list_of_value t4 on t3.type_id = t4.id " +
            "inner join s_user_role t5 on t1.id = t5.user_id " +
            "inner join s_role t6 on t6.id = t5.role_id " +
            "inner join s_customer_activity t7 ON t7.activity_id = t3.id " +
            "inner join s_customer t8 on t8.id = t7.customer_id " +
            "where t6.role_name = 'consultant' and t4.type like '%S_LOV_SALES%' " +
            "and YEAR(t3.time) = YEAR(#{date}) and MONTH(t3.time) = MONTH(#{date}) and t3.status = 2 group by t1.id ,t1.nickname,t4.value")
    List<SalesCommon> getActNumberByTypeAndMonthAll(@Param("date") String date);

    @Select("select t1.id, t1.nickname as name,count(*) as value1,sum(t4.amount) as value2 from s_user t1 " +
            "inner join s_user_customer t2 on t1.id = t2.user_id " +
            "inner join s_customer t3 on t3.id = t2.customer_id " +
            "inner join s_contract t4 on t3.id = t4.customer_id " +
            "where t2.roleplay = 'consultant' " +
            "and YEAR(t4.create_time) = YEAR(#{date}) and MONTH(t4.create_time) = MONTH(#{date}) " +
            "group by t1.id, t1.nickname")
    List<SalesPerf> getContractNumberAndAmountByMonthAll(@Param("date") String date);

    @Select("select t1.id,t1.nickname as name,t4.value as type,count(t4.value) as value from s_user t1 " +
            "inner join s_user_activity t2 on t1.id = t2.user_id " +
            "inner join s_activity t3 on t3.id = t2.activity_id " +
            "inner join s_list_of_value t4 on t3.type_id = t4.id " +
            "inner join s_user_role t5 on t1.id = t5.user_id " +
            "inner join s_role t6 on t6.id = t5.role_id " +
            "inner join s_customer_activity t7 ON t7.activity_id = t3.id " +
            "inner join s_customer t8 on t8.id = t7.customer_id " +
            "where t6.role_name = 'consultant' and t4.type like '%S_LOV_SALES%' " +
            "and YEAR(t3.time) = YEAR(#{date}) and WEEK(t3.time,1) = WEEK(#{date},1) and t3.status = 2 group by t1.id ,t1.nickname,t4.value")
    List<SalesCommon> getActNumberByTypeAndWeekAll(@Param("date") String date);

    @Select("select t1.id, t1.nickname as name,count(*) as value1,sum(t4.amount) as value2 from s_user t1 " +
            "inner join s_user_customer t2 on t1.id = t2.user_id " +
            "inner join s_customer t3 on t3.id = t2.customer_id " +
            "inner join s_contract t4 on t3.id = t4.customer_id " +
            "where t2.roleplay = 'consultant' " +
            "and YEAR(t4.create_time) = YEAR(#{date}) and WEEK(t4.create_time,1) = WEEK(#{date},1) " +
            "group by t1.id, t1.nickname")
    List<SalesPerf> getContractNumberAndAmountByWeekAll(@Param("date") String date);

    @Select("select t1.id,t1.nickname as name,t4.value as type,count(t4.value) as value from s_user t1 " +
            "inner join s_user_activity t2 on t1.id = t2.user_id " +
            "inner join s_activity t3 on t3.id = t2.activity_id " +
            "inner join s_list_of_value t4 on t3.type_id = t4.id " +
            "inner join s_user_role t5 on t1.id = t5.user_id " +
            "inner join s_role t6 on t6.id = t5.role_id " +
            "inner join s_customer_activity t7 ON t7.activity_id = t3.id " +
            "inner join s_customer t8 on t8.id = t7.customer_id " +
            "where t6.role_name = 'consultant' and t4.type like '%S_LOV_SALES%' " +
            "and DATE(t3.time) = DATE(#{date}) and t3.status = 2 group by t1.id ,t1.nickname,t4.value")
    List<SalesCommon> getActNumberByTypeAndDayAll(@Param("date") String date);

    @Select("select t1.id, t1.nickname as name,count(*) as value1,sum(t4.amount) as value2 from s_user t1 " +
            "inner join s_user_customer t2 on t1.id = t2.user_id " +
            "inner join s_customer t3 on t3.id = t2.customer_id " +
            "inner join s_contract t4 on t3.id = t4.customer_id " +
            "where t2.roleplay = 'consultant' " +
            "and DATE(t4.create_time) = DATE(#{date}) " +
            "group by t1.id, t1.nickname")
    List<SalesPerf> getContractNumberAndAmountByDayAll(@Param("date") String date);

    @Select("select t1.id,t1.nickname as name,count(t4.value) as value from s_user t1 " +
            "inner join s_user_activity t2 on t1.id = t2.user_id " +
            "inner join s_activity t3 on t3.id = t2.activity_id " +
            "inner join s_list_of_value t4 on t3.type_id = t4.id " +
            "inner join s_user_role t5 on t1.id = t5.user_id " +
            "inner join s_role t6 on t6.id = t5.role_id " +
            "inner join s_customer_activity t7 ON t7.activity_id = t3.id " +
            "inner join s_customer t8 on t8.id = t7.customer_id " +
            "where t6.role_name = 'consultant' and t2.roleplay = 'consultant' and t4.type = #{type} and t1.username =#{username} " +
            "and YEAR(t3.time) = YEAR(#{date}) and MONTH(t3.time) = MONTH(#{date}) and t3.status = 2 group by t1.id ,t1.nickname")
    NameValueItems getActNumberByTypeAndMonth(@Param("username") String username, @Param("type") String type, @Param("date") String date);

    @Select("select t1.id, t1.nickname as name,count(t3.name) as value from s_user t1 " +
            "inner join s_user_customer t2 on t1.id = t2.user_id " +
            "inner join s_customer t3 on t3.id = t2.customer_id " +
            "inner join s_contract t4 on t3.id = t4.customer_id " +
            "where t2.roleplay = 'consultant' " +
            "and YEAR(t4.create_time) = YEAR(#{date}) and MONTH(t4.create_time) = MONTH(#{date}) and t1.username =#{username} " +
            "group by t1.id, t1.nickname")
    NameValueItems getContractNumberByMonth(@Param("username") String username, @Param("date") String date);

    @Select("select t1.id, t1.nickname as name,sum(t4.amount) as value from s_user t1 " +
            "inner join s_user_customer t2 on t1.id = t2.user_id " +
            "inner join s_customer t3 on t3.id = t2.customer_id " +
            "inner join s_contract t4 on t3.id = t4.customer_id " +
            "where t2.roleplay = 'consultant' " +
            "and YEAR(t4.create_time) = YEAR(#{date}) and MONTH(t4.create_time) = MONTH(#{date}) and t1.username =#{username} " +
            "group by t1.id, t1.nickname")
    NameValueItems getContractAmountByMonth(@Param("username") String username, @Param("date") String date);

    @Select("select t1.id,t1.nickname as name,count(t4.value) as value from s_user t1 " +
            "inner join s_user_activity t2 on t1.id = t2.user_id " +
            "inner join s_activity t3 on t3.id = t2.activity_id " +
            "inner join s_list_of_value t4 on t3.type_id = t4.id " +
            "inner join s_user_role t5 on t1.id = t5.user_id " +
            "inner join s_role t6 on t6.id = t5.role_id " +
            "inner join s_customer_activity t7 ON t7.activity_id = t3.id " +
            "inner join s_customer t8 on t8.id = t7.customer_id " +
            "where t6.role_name = 'consultant' and t2.roleplay = 'consultant' and t4.type = #{type} and t1.username =#{username} " +
            "and YEAR(t3.time) = YEAR(#{date}) and WEEK(t3.time,1) = WEEK(#{date},1) and t3.status = 2 group by t1.id ,t1.nickname")
    NameValueItems getActNumberByTypeAndWeek(@Param("username") String username, @Param("type") String type, @Param("date") String date);

    @Select("select t1.id, t1.nickname as name,count(t3.name) as value from s_user t1 " +
            "inner join s_user_customer t2 on t1.id = t2.user_id " +
            "inner join s_customer t3 on t3.id = t2.customer_id " +
            "inner join s_contract t4 on t3.id = t4.customer_id " +
            "where t2.roleplay = 'consultant' " +
            "and YEAR(t4.create_time) = YEAR(#{date}) and WEEK(t4.create_time,1) = WEEK(#{date},1) and t1.username =#{username} " +
            "group by t1.id, t1.nickname")
    NameValueItems getContractNumberByWeek(@Param("username") String username, @Param("date") String date);

    @Select("select t1.id, t1.nickname as name,sum(t4.amount) as value from s_user t1 " +
            "inner join s_user_customer t2 on t1.id = t2.user_id " +
            "inner join s_customer t3 on t3.id = t2.customer_id " +
            "inner join s_contract t4 on t3.id = t4.customer_id " +
            "where t2.roleplay = 'consultant' " +
            "and YEAR(t4.create_time) = YEAR(#{date}) and WEEK(t4.create_time,1) = WEEK(#{date},1) and t1.username =#{username} " +
            "group by t1.id, t1.nickname")
    NameValueItems getContractAmountByWeek(@Param("username") String username, @Param("date") String date);

    @Select("select t1.id,t1.nickname as name,count(t4.value) as value from s_user t1 " +
            "inner join s_user_activity t2 on t1.id = t2.user_id " +
            "inner join s_activity t3 on t3.id = t2.activity_id " +
            "inner join s_list_of_value t4 on t3.type_id = t4.id " +
            "inner join s_user_role t5 on t1.id = t5.user_id " +
            "inner join s_role t6 on t6.id = t5.role_id " +
            "inner join s_customer_activity t7 ON t7.activity_id = t3.id " +
            "inner join s_customer t8 on t8.id = t7.customer_id " +
            "where t6.role_name = 'consultant' and t2.roleplay = 'consultant' and t4.type = #{type} and t1.username =#{username} " +
            "and DATE(t3.time) = DATE(#{date}) and t3.status = 2 group by t1.id ,t1.nickname")
    NameValueItems getActNumberByTypeAndDay(@Param("username") String username, @Param("type") String type, @Param("date") String date);

    @Select("select t1.id, t1.nickname as name,count(t3.name) as value from s_user t1 " +
            "inner join s_user_customer t2 on t1.id = t2.user_id " +
            "inner join s_customer t3 on t3.id = t2.customer_id " +
            "inner join s_contract t4 on t3.id = t4.customer_id " +
            "where t2.roleplay = 'consultant' " +
            "and DATE(t4.create_time) = DATE(#{date}) and t1.username =#{username} " +
            "group by t1.id, t1.nickname")
    NameValueItems getContractNumberByDay(@Param("username") String username, @Param("date") String date);

    @Select("select t1.id, t1.nickname as name,sum(t4.amount) as value from s_user t1 " +
            "inner join s_user_customer t2 on t1.id = t2.user_id " +
            "inner join s_customer t3 on t3.id = t2.customer_id " +
            "inner join s_contract t4 on t3.id = t4.customer_id " +
            "where t2.roleplay = 'consultant' " +
            "and DATE(t4.create_time) = DATE(#{date}) and t1.username =#{username} " +
            "group by t1.id, t1.nickname")
    NameValueItems getContractAmountByDay(@Param("username") String username, @Param("date") String date);

    @Select("select id, name, count(phone) as value from " +
            "(select distinct t1.id, t1.nickname as name, t3.phone from s_user t1 " +
            "inner join s_user_customer t2 on t1.id = t2.user_id " +
            "inner join s_customer t3 on t3.id = t2.customer_id " +
            "inner join s_user_role t4 on t4.user_id = t1.id " +
            "inner join s_role t5 on t4.role_id = t5.id " +
            "where t3.status = #{customer_type} and t1.username =#{username} " +
            "and t5.role_name = 'consultant' and t2.roleplay = 'consultant') tb1 " +
            "group by id, name")
    NameValueItems getCusNumberByType(@Param("username") String username, @Param("customer_type") Integer customer_type);

    @Select("select t1.id,t1.username,t1.nickname from s_user t1 " +
            "inner join s_user_role t2 on t1.id = t2.user_id " +
            "inner join s_role t3 on t3.id = t2.role_id " +
            "where t1.status = 1 and t3.role_name = 'consultant'")
    List<SysUser> getSalesList();

    @Select("select id,name,DAY(contract_date) as value1,(select sum(amount) from (select t1.nickname as name,t2.start_date as contract_date,sum(t2.amount) as amount from s_user t1 " +
            "inner join s_contract t2 on t1.id = t2.user_id " +
            "where YEAR(t2.start_date) = YEAR(#{date}) and MONTH(t2.start_date) and MONTH(#{date}) " +
            "group by t1.nickname, t2.start_date order by t1.nickname,t2.start_date asc) A where name = B.name and contract_date <= B.contract_date) as value2 " +
            "from " +
            "(" +
            "select t1.id,t1.nickname as name,t2.start_date as contract_date,sum(t2.amount) as amount from s_user t1 " +
            "inner join s_contract t2 on t1.id = t2.user_id " +
            "where YEAR(t2.start_date) = YEAR(#{date}) and MONTH(t2.start_date) and MONTH(#{date}) " +
            "group by t1.id,t1.nickname,t2.start_date order by t1.nickname,t2.start_date asc " +
            ") B")
    List<SalesPerf> getSalesPerfByDay(String date);

    @Select("select id,name,DAY(contract_date) as value1,(select sum(amount) from (select t1.nickname as name,t2.start_date as contract_date,sum(t2.amount) as amount from s_user t1 " +
            "inner join s_contract t2 on t1.id = t2.user_id " +
            "where YEAR(t2.start_date) = YEAR(#{date}) and MONTH(t2.start_date) = MONTH(#{date}) and t1.username=#{username} " +
            "group by t1.nickname, t2.start_date order by t1.nickname,t2.start_date asc) A where id = B.id and contract_date <= B.contract_date) as value2 " +
            "from " +
            "(" +
            "select t1.id,t1.nickname as name,t2.start_date as contract_date,sum(t2.amount) as amount from s_user t1 " +
            "inner join s_contract t2 on t1.id = t2.user_id " +
            "where YEAR(t2.start_date) = YEAR(#{date}) and MONTH(t2.start_date) = MONTH(#{date}) and t1.username=#{username} " +
            "group by t1.id,t1.nickname,t2.start_date order by t1.nickname,t2.start_date asc " +
            ") B")
    List<SalesPerf> getSalesPerfByDayWithName(@Param("date") String date, @Param("username") String username);

    @Select("select id,quality_tag,name," +
            "DATE_FORMAT(FROM_DAYS(TO_DAYS(NOW())-TO_DAYS(birthday)), '%Y')+0 AS age,channel_tag," +
            "DATEDIFF(NOW(),create_time) as days from s_customer " +
            "where (status = 0 or status = 8) and id not in " +
            "(select distinct customer_id from s_user_customer where roleplay = 'consultant' and customer_id is not null) order by ${orderBy} ${sequence}")
    List<CustomerNotAssignedModel> getCustomerNotAssignedModel(@Param("orderBy") String orderBy, @Param("sequence") String sequence);
}